package appbuilder.wizards.data;

import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Collection;

import appbuilder.application.data.Column;
import appbuilder.application.data.DataSource;
import appbuilder.application.data.DataType;
import appbuilder.application.data.Table;

public class DataWizard {
	private static final String DECIMAL_DIGITS = "DECIMAL_DIGITS";
	private static final String COLUMN_SIZE = "COLUMN_SIZE";
	private static final String IS_NULLABLE = "IS_NULLABLE";
	private static final String TYPE_NAME = "TYPE_NAME";
	private static final String COLUMN_NAME = "COLUMN_NAME";
	private static final String TABLE_NAME = "TABLE_NAME";
	private static final String SCHEMA_NAME = "TABLE_SCHEM";
	private static final String CATALOG_NAME = "TABLE_CAT";
	private static final String WILDCARD = "%";
	
	private Connection connection;
	
	public DataWizard(String driver, String url, String user, String password) throws Exception {
		Class.forName(driver);
		this.connection = DriverManager.getConnection(url, user, password);
	}
	
	public DataSource getDataSource() throws Exception {
		DatabaseMetaData metaData = connection.getMetaData();
		ResultSet ctgRs = metaData.getCatalogs();
		DataSource ds = new DataSource("");
		
		while(ctgRs.next()) {
			String catalogName = ctgRs.getString(CATALOG_NAME);
			ds.setName(catalogName);
			
			ResultSet schRs = null;
			boolean hasSchema = false;
			try {
				schRs = metaData.getSchemas(catalogName, WILDCARD);
				
				while(schRs.next()) {
					String schemaName = schRs.getString(SCHEMA_NAME);
					loadSchema(metaData, ds, catalogName, schemaName);
					hasSchema = true;
				}
			} catch (UnsupportedOperationException ex) {
			}
			
			if (!hasSchema) {
				loadSchema(metaData, ds, catalogName, null);
			}
		}
		
		return ds;
	}
	
	private void loadSchema(DatabaseMetaData metaData, DataSource ds, String catalogName, String schemaName) throws Exception {
		ResultSet tblRs = metaData.getTables(catalogName, schemaName, WILDCARD, null);
		while(tblRs.next()) {
			String tableName = tblRs.getString(TABLE_NAME);
			loadMethod(metaData, ds, catalogName, schemaName, tableName);
		}
	}

	private void loadMethod(DatabaseMetaData metaData, DataSource ds,
			String catalogName, String schemaName, String tableName)
			throws SQLException {
		Table table = new Table(tableName, schemaName);
		
		Collection<String> primaryKeys = loadPrimaryKeys(metaData, catalogName, schemaName, tableName);
		
		//Discovering the foreign keys referencing this table
		//TODO - Implement this
		
		ResultSet rsCol = metaData.getColumns(catalogName, schemaName, tableName, WILDCARD);
		while(rsCol.next()) {
			table.addColumn(loadColumn(rsCol, primaryKeys));
		}
		
		ds.addTable(table);
	}
	
	private Collection<String> loadPrimaryKeys(DatabaseMetaData metaData, String catalogName, String schemaName, String tableName) throws SQLException {
		Collection<String> primaryKeys = new ArrayList<String>();
		
		//Storing the primary keys
		ResultSet pkeyRs = metaData.getPrimaryKeys(catalogName, schemaName, tableName);
		while(pkeyRs.next()) {
			primaryKeys.add(pkeyRs.getString(COLUMN_NAME));
		}
		
		return primaryKeys;
	}
	
	private Column loadColumn(ResultSet rsCol, Collection<String> primaryKeys) throws SQLException {
		String columnName = rsCol.getString(COLUMN_NAME);
		String typeName   = rsCol.getString(TYPE_NAME);
		
		DataType type = null;
		
		for(DataType tp : DataType.values()) {
			if (tp.toString().equalsIgnoreCase(typeName)) {
				type = tp;
				break;
			}
		}
		
		if (type == null) {
			type = DataType.VARCHAR;
		}
		
		Column column = new Column(columnName, type);
		column.setNullable("YES".equals(rsCol.getString(IS_NULLABLE)));
		column.setPrimaryKey(primaryKeys.contains(columnName));
		column.setPrecision(0, rsCol.getInt(COLUMN_SIZE));
		
		if (column.getType().getDimensions() > 1) {
			column.setPrecision(1, rsCol.getInt(DECIMAL_DIGITS));
		}
		
		return column;
	}
	
	public static void main(String[] args) throws Exception {
		DataWizard wizard = new DataWizard("com.mysql.jdbc.Driver", "jdbc:mysql://localhost/test", "test", "test");
		DataSource ds = wizard.getDataSource();
		System.out.println(ds);
	}
}
